# -*- coding:utf-8 -*-
"""
操作数据库

"""
import time
import psycopg2

class ADhandler(object):

    def __init__(self):
        super(ADhandler, self).__init__()



    def _init_db_handler(self):
        connect = psycopg2.connect(database='ad',
                                   user='timeaxis',
                                   password='timeaxis',
                                   host='192.168.1.222',
                                   port=5432)
        cursor = connect.cursor()

        return connect,cursor



    def init_table(self):
        success = False
        sql = "create table info(_id serial PRIMARY KEY," \
              "_name character varying(100)," \
              "_type integer," \
              "_title character varying(100)," \
              "_pub_time integer," \
              "_end_time integer," \
              "_text text);"
        try:
            connect, cursor = self._init_db_handler()
            cursor.execute(sql)
            connect.commit()
            success = True
        except Exception as ERR:
            print('ERR:',ERR)
        finally:
            connect.close()

        return success

    def get_data(self,_type:int):
        """
        传入类型索引，获取该类型所有任务
        :param _type:
        :return:
        """
        data_list = []
        now = int(time.time())
        sql = f"select _id,_name,_type,_title,_pub_time," \
              f"_end_time,_text " \
              f"from info where _type = {_type} and _end_time>{now} "
        try:
            connect, cursor = self._init_db_handler()
            cursor.execute(sql)
            result = cursor.fetchall()
            if result:
                for i in result:
                    data = {}
                    data['_id'], data['_name'], data['_type'], data['_title'],data['_pub_time'],\
                    data['_end_time'], data['_text'] = i
                    data_list.append(data)
        except Exception as get_data_ERR:
            print('get_data_ERR:',str(get_data_ERR))

        finally:
            connect.close()

        return data_list


    def set_data(self,data):
        success = False
        if data:
            _name = data.get('_name','')
            _type = data.get('_type',-1)
            _title = data.get('_title','')
            _pub_time = data.get('_pub_time',0)
            _end_time = data.get('_end_time',0)
            _text = data.get('_text','')

            sql = "insert into info(_name,_type,_title,_pub_time,_end_time," \
                  "_text) " \
                  "values " \
                  f"('{_name}',{_type},'{_title}','{_pub_time}','{_end_time}'," \
                  f"'{_text}')"

            try:
                connect, cursor = self._init_db_handler()
                cursor.execute(sql)
                connect.commit()
                success = True
            except Exception as get_data_ERR:
                print('get_data_ERR:', str(get_data_ERR))
            finally:
                connect.close()

        return success

if __name__ == '__main__':

    d = ADhandler()
    result = d.init_table()
    print(result)